package data.CourseData;

import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import po.coursePO.CoursePO;
import dataservice.ResultMessage;
import dataservice.courseDataService.CourseDatabaseService;

public class CourseData extends UnicastRemoteObject implements
		CourseDatabaseService {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public CourseData() throws RemoteException {
		super();
	}

	@Override
	public dataservice.ResultMessage insert(CoursePO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String head = "INSERT INTO `选课管理系统`.`course`";
			String colomnString = "(`couID`,`couName`, `credit`, `coursePeriod`, `classroom`, `assistant`, `teaID`,`departName`,`attribute`,`book`,`introduction`,`courseTime`,`maxStudent`,`term`)";
			String dataString = "VALUES (" + getValue(po.getCourseId());
			dataString = dataString + "," + getValue(po.getCourseName());
			dataString = dataString + "," + "'"
					+ Integer.toString(po.getCredit()) + "'";
			dataString = dataString + "," + "'"
					+ Integer.toString(po.getCoursePeriod()) + "'";
			dataString = dataString + "," + getValue(po.getClassroom());
			dataString = dataString + "," + getValue(po.getAssistant());
			dataString = dataString + "," + getValue(po.getTeacherID());
			dataString = dataString + "," + getValue(po.getDepartmentID());
			dataString = dataString + "," + getValue(po.getAttribute());
			dataString = dataString + "," + getValue(po.getBook());
			dataString = dataString + "," + getValue(po.getIntroduction());
			dataString = dataString + "," + getValue(po.getCourseTime());
			dataString = dataString + "," + "'"
					+ Integer.toString(po.getMaxStudent()) + "'";
			dataString = dataString + "," + "'"
					+ Integer.toString(po.getTerm()) + "'" + ")";
			String sqlString = head + colomnString + dataString;
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1062) {
				return ResultMessage.INSERT_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1364) {
				return ResultMessage.INSERT_ISNULL;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.INSERT_SUCCESS;
		} else {
			return ResultMessage.INSERT_FAILURE;
		}
	}

	@Override
	public dataservice.ResultMessage delete(CoursePO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "DELETE FROM `选课管理系统`.`course` "
					+ "WHERE `couID` = '" + po.getCourseId() + "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.DELETE_SUCCESS;
		} else {
			return ResultMessage.DELETE_FAILURE;
		}
	}

	@Override
	public dataservice.ResultMessage update(CoursePO po) {
		int result = -1;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "UPDATE `选课管理系统`.`course` SET " + "couName="
					+ getValue(po.getCourseName()) + "," + "credit='"
					+ Integer.toString(po.getCredit()) + "',"
					+ "coursePeriod='" + Integer.toString(po.getCoursePeriod())
					+ "'," + "classroom=" + getValue(po.getClassroom()) + ","
					+ "assistant=" + getValue(po.getAssistant()) + ","
					+ "teaID=" + getValue(po.getTeacherID()) + ","
					+ "departName=" + getValue(po.getDepartmentID()) + ","
					+ "attribute=" + getValue(po.getAttribute()) + ","
					+ "book=" + getValue(po.getBook()) + "," + "introduction="
					+ getValue(po.getIntroduction()) + "," + "courseTime="
					+ getValue(po.getCourseTime()) + "," + "maxStudent='"
					+ Integer.toString(po.getMaxStudent()) + "'"
					+ "," + "term='" + Integer.toString(po.getTerm()) + "'";
			sqlString = sqlString + " WHERE `couID` = '" + po.getCourseId()
					+ "'";
			System.out.println(sqlString);
			result = statement.executeUpdate(sqlString);
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
			if (e.getErrorCode() == 1452) {
				return ResultMessage.UPDATE_FOREIGNKEY_NOT_EXIST;
			}
			if (e.getErrorCode() == 1062) {
				return ResultMessage.UPDATE_DEPLICATE_PRIMARY;
			}
			if (e.getErrorCode() == 1048) {
				return ResultMessage.UPDATE_ISNULL;
			}
			if (e.getErrorCode() == 1406) {
				return ResultMessage.UPDATE_TOO_LONG;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result == 1) {
			return ResultMessage.UPDATE_SUCCESS;
		} else {
			return ResultMessage.UPDATE_FAILURE;
		}
	}

	@Override
	public CoursePO find(String id) {
		CoursePO coursePO = null;
		String sqlString = "SELECT course.*,teacher.teaName ";
		sqlString += "FROM course LEFT JOIN teacher ON course.teaID=teacher.teaID ";
		sqlString += "WHERE course.couID='" + id + "'";
		System.out.println(sqlString);
		coursePO = changeToCoursePO(sqlString);
		return coursePO;
	}

	@Override
	public ArrayList<CoursePO> findAll() {
		String sqlString = "SELECT course.*,teacher.teaName ";
		sqlString += " FROM course LEFT JOIN teacher ON course.teaID=teacher.teaID";
		ArrayList<CoursePO> coursePOs = changeToCoursePOs(sqlString);
		return coursePOs;
	}

	@Override
	public ArrayList<CoursePO> findSelectableCourse(String departmentID) {
		ArrayList<CoursePO> coursePOs = new ArrayList<CoursePO>();
		String sqlString = "SELECT course.*,teacher.teaName ";
		sqlString += "FROM course LEFT JOIN teacher ON course.teaID=teacher.teaID ";
		sqlString += "WHERE course.departName='" + departmentID
				+ "' AND course.attribute='开放选修'";
		System.out.println(sqlString);
		coursePOs = changeToCoursePOs(sqlString);
		return coursePOs;
	}

	@Override
	public ArrayList<CoursePO> findSelectableCourses() {
		ArrayList<CoursePO> coursePOs = new ArrayList<CoursePO>();
		String sqlString = "SELECT course.*,teacher.teaName ";
		sqlString += "FROM course LEFT JOIN teacher ON course.teaID=teacher.teaID ";
		sqlString += " WHERE course.attribute='开放选修'";
		System.out.println(sqlString);
		coursePOs = changeToCoursePOs(sqlString);
		return coursePOs;
	}

	private String getValue(String valueString) {
		String result = null;
		if (!((valueString == null) || (valueString.isEmpty()))) {
			result = "'" + valueString + "'";
		} else {
			result = "NULL";
		}
		return result;
	}

	@Override
	public ArrayList<String> findAllDepartments() {
		ArrayList<String> strings = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			String sqlString = "SELECT department.*";
			sqlString += " FROM department";
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				String departName = rs.getString(1);
				strings.add(departName);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strings;
	}

	@Override
	public ArrayList<CoursePO> findCourseByDepartment(String department)
			throws RemoteException {
		ArrayList<CoursePO> coursePOs = new ArrayList<CoursePO>();
		String sqlString = "SELECT course.*,teacher.teaName ";
		sqlString += "FROM course LEFT JOIN teacher ON course.teaID=teacher.teaID ";
		sqlString += "WHERE course.departName='" + department + "'";
		System.out.println(sqlString);
		coursePOs = changeToCoursePOs(sqlString);
		return coursePOs;
	}

	@Override
	public ArrayList<CoursePO> findCourseByTeacherID(String teacherID)
			throws RemoteException {
		ArrayList<CoursePO> coursePOs = new ArrayList<CoursePO>();
		String sqlString = "SELECT course.*,teacher.teaName ";
		sqlString += "FROM course LEFT JOIN teacher ON course.teaID=teacher.teaID ";
		sqlString += " WHERE course.teaID='" + teacherID + "'";
		System.out.println(sqlString);
		coursePOs = changeToCoursePOs(sqlString);
		return coursePOs;
	}

	private CoursePO changeToCoursePO(String sqlString) {
		CoursePO coursePO = null;
		String couID = null;
		String couName = null;
		int credit = 0;
		int courseperiod = 0;
		String classroom = null;
		String assist = null;
		String teaId = null;
		String departName = null;
		String attribute = null;
		String book = null;
		String introduction = null;
		String courseTime = null;
		String teaName = null;
		int term =0;
		int maxStudent = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlString);
			if (rs.next()) {
				couID = rs.getString(1);
				couName = rs.getString(2);
				credit = rs.getInt(3);
				courseperiod = rs.getInt(4);
				classroom = rs.getString(5);
				assist = rs.getString(6);
				teaId = rs.getString(7);
				departName = rs.getString(8);
				attribute = rs.getString(9);
				book = rs.getString(10);
				introduction = rs.getString(11);
				courseTime = rs.getString(12);
				maxStudent = rs.getInt(13);
				term = rs.getInt(14);
				teaName = rs.getString(15);
				coursePO = new CoursePO(couID, couName, teaId, teaName, credit,
						courseperiod, classroom, assist, courseTime, attribute,
						maxStudent, term, departName, book, introduction);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return coursePO;
	}

	private ArrayList<CoursePO> changeToCoursePOs(String sqlString) {
		ArrayList<CoursePO> coursePOs = new ArrayList<CoursePO>();
		CoursePO coursePO = null;
		String couID = null;
		String couName = null;
		int credit = 0;
		int courseperiod = 0;
		String classroom = null;
		String assist = null;
		String teaId = null;
		String departName = null;
		String attribute = null;
		String book = null;
		String introduction = null;
		String courseTime = null;
		String teaName = null;
		int term =0;
		int maxStudent = 0;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/选课管理系统", "root", "");
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(sqlString);
			while (rs.next()) {
				couID = rs.getString(1);
				couName = rs.getString(2);
				credit = rs.getInt(3);
				courseperiod = rs.getInt(4);
				classroom = rs.getString(5);
				assist = rs.getString(6);
				teaId = rs.getString(7);
				departName = rs.getString(8);
				attribute = rs.getString(9);
				book = rs.getString(10);
				introduction = rs.getString(11);
				courseTime = rs.getString(12);
				maxStudent = rs.getInt(13);
				term = rs.getInt(14);
				teaName = rs.getString(15);
				coursePO = new CoursePO(couID, couName, teaId, teaName, credit,
						courseperiod, classroom, assist, courseTime, attribute,
						maxStudent, term, departName, book, introduction);
				coursePOs.add(coursePO);
			}
		} catch (SQLException e) {
			System.out.printf("ErrorCode is %d\n", e.getErrorCode());
			System.out.printf("ErrorMessage is %s\n", e.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return coursePOs;
	}
}